import { Sequelize, sequelize } from './basemodel.js';

// Override timezone formatting
Sequelize.DATE.prototype._stringify = function _stringify(date, options) {
	return this._applyTimezone(date, options).format('YYYY-MM-DD HH:mm:ss');
};

import Audits from './audits.js';
import Blocks from './blocks.js';
import Courses from './courses.js';
import Enrollments from './enrollments.js';
import Grades from './grades.js';
import Permissions from './permissions.js';
import Roles from './roles.js';
import SmartContracts from './smartcontracts.js';
import SmartContractTransactions from './smartcontracttransactions.js';
import Studentgradesummary from './studentgradesummary.js';
import Users from './users.js';
import EmployerApplications from './employerapplications.js';
import ApiKeys from './apikeys.js';

// 定义模型关联关系
Users.hasMany(Enrollments, { as: 'enrollments', foreignKey: 'student_id' });
Enrollments.belongsTo(Users, { as: 'student', foreignKey: 'student_id', targetKey: 'user_id'});
Grades.belongsTo(Enrollments, { as:'enrollment', foreignKey:'enrollment_id', targetKey: 'enrollment_id'});
Enrollments.belongsTo(Courses, { as:'course', foreignKey:'course_id', targetKey: 'course_id'});
Enrollments.hasMany(Grades, { as:'grades', foreignKey:'enrollment_id', sourceKey: 'enrollment_id'});
Courses.belongsTo(Users, { as: 'teacher', foreignKey: 'teacher_id', targetKey: 'user_id'});

// 企业申请相关关联
EmployerApplications.hasMany(ApiKeys, {as: 'api_keys', foreignKey: 'employer_id'});
ApiKeys.belongsTo(EmployerApplications, {as: 'employer', foreignKey: 'employer_id'});
Users.hasMany(EmployerApplications, {as: 'reviewed_applications', foreignKey: 'reviewer_id'});

// 成绩汇总相关关联
Users.hasMany(Studentgradesummary, { as: 'grade_summaries', foreignKey: 'student_id' });
Studentgradesummary.belongsTo(Users, { as: 'student', foreignKey: 'student_id', targetKey: 'user_id' });

const op = Sequelize.Op;
const raw = Sequelize.literal;

const filterBy = function(expression, value){
	return sequelize.where(raw(expression), value);
}

// 添加忽略大小写的查询方法
const filterByCaseInsensitive = function(field, value) {
    return sequelize.where(
        sequelize.fn('LOWER', sequelize.col(field)),
        '=',
        value.toLowerCase()
    );
}

// convinient functions for performing raw queries 
// return different value types

function rawQuery(queryText, options){
	return sequelize.query(queryText, options);
}

async function rawQueryList(queryText, queryParams){
	const records = await rawQuery(queryText, { replacements: queryParams, type: Sequelize.QueryTypes.SELECT });
	return records;
}

async function rawQueryOne(queryText, queryParams){
	const records = await rawQueryList(queryText, queryParams);
	return records[0] || null;
}

async function rawQueryValue(queryText, queryParams){
	const record = await rawQueryOne(queryText, queryParams);
	if(record){
		return Object.values(record)[0];
	}
	return null;
}

function getOrderBy(req, sortField = null, sortType = 'desc'){
	const orderBy = req.query.orderby || sortField;
	const orderType = req.query.ordertype || sortType;
	if (orderBy) {
		let order = raw(`${orderBy} ${orderType}`);
		return [[order]];
	}
	return null;
}

const DB = {
	sequelize,
	op,
	filterBy,
	filterByCaseInsensitive, // 添加新方法
	raw,
	rawQuery,
	rawQueryList,
	rawQueryOne,
	rawQueryValue,
	getOrderBy,
	Audits,
	Blocks,
	Courses,
	Enrollments,
	Grades,
	Permissions,
	Roles,
	SmartContracts,
	SmartContractTransactions,
	Studentgradesummary,
	Users,
	EmployerApplications,
	ApiKeys
};

export default DB;
